//Made By Prafull Goyal
//Please Dont Change In this class

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.Configuration;

public enum Actions
{
    Select,
    Insert,
    Update,
    Delete
    
}
public class clsDBOperation : DbProviderFactory 
{
    String strConnection = "";
    String providerName = "";
    String Collected_by = "";
    DbProviderFactory Provider;

    public static DbConnection conn;
    Hashtable hstParameters1 = new Hashtable();
    Hashtable hstParameter = new Hashtable();
    string address = "";

    public clsDBOperation()
    {
        Provider = GetProvider();
        funcon();
    }
    public DbProviderFactory GetProvider()
    {
        providerName = "System.Data.SqlClient";
        Provider = DbProviderFactories.GetFactory(providerName);
        return Provider;
    }
    public bool funcon()
    {
        SqlConnection con = new SqlConnection();
        if (!File.Exists(HttpContext.Current.Server.MapPath("~/c o n n e c t i o n s t r i n g .txt")))
            return (false);
        else
        {
            FileInfo fi = new FileInfo(HttpContext.Current.Server.MapPath("~/c o n n e c t i o n s t r i n g .txt"));
            StreamReader sr = fi.OpenText();
            strConnection = sr.ReadToEnd();
            //conn.ConnectionTimeout() = 30;
            conn = Provider.CreateConnection();
            conn.ConnectionString = strConnection;
            sr.Close();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close(); 
                conn.Dispose();
            }
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            fi = null;
            return (true);
        }
    }
   

    public void fillddl(System.Web.UI.WebControls.DropDownList ddl, string action_name, string sp_name, string valfield, string txtfield, System.Web.UI.WebControls.Label lblmsg)
    {
        try
        {
            DataSet dsddl = new DataSet();
            hstParameter.Clear();
            hstParameter.Add("Action", action_name);
            dsddl.Merge((DataSet)GetDataset(sp_name, CommandType.StoredProcedure, hstParameter));
            ddl.Items.Clear();
            if (dsddl.Tables.Count > 0)
            {
                if (dsddl.Tables[0].Rows.Count > 0)
                {
                    ddl.DataTextField = txtfield;
                    ddl.DataValueField = valfield;
                    ddl.DataSource = dsddl;
                    ddl.DataBind();
                   
                }
            }
            ddl.Items.Insert(0, new ListItem("---Select One----", "-1"));
        }
        catch (SqlException ex)
        {
            lblmsg.Text = ex.Message;
            lblmsg.EnableViewState = false;

        }
    }
    public SqlConnection opencon()
    {
        SqlConnection con = new SqlConnection();
        if (con.State == ConnectionState.Closed)
        {
            FileInfo fi = new FileInfo(HttpContext.Current.Server.MapPath("~/c o n n e c t i o n s t r i n g .txt"));
            StreamReader sr = fi.OpenText();
            strConnection = sr.ReadToEnd();
            con.ConnectionString = strConnection;
            sr.Close();
            if (con.State == ConnectionState.Open)
            {
                con.Close();
                con.Dispose();
            }
            if (con.State == ConnectionState.Closed)
                con.Open();
            fi = null;
        }
        return (con);
    }
    //public void MessageBox(System.Web.UI.Control Page, System.String msg )
    //{
    //    ScriptManager.RegisterStartupScript(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('" + msg + "')", true);
    //}

   public Boolean chkphoto(System.Web.UI.WebControls.FileUpload flu)
    {
        Boolean Flag = new Boolean();
        Flag = false;
        Boolean fileExtensionType = new Boolean();
        string fileExtension;
        if (flu.HasFile == true)
        {

            fileExtension = System.IO.Path.GetExtension(flu.FileName).ToLower();
            String[] allowedExtensions = { ".jpg", ".jpeg", ".gif", ".bmp" };
            for (int i = 0; i < allowedExtensions.Length; i++)
            {
                if (fileExtension.ToString().Trim() == allowedExtensions[i].ToString().Trim())
                {
                    fileExtensionType = true;
                    i = allowedExtensions.Length;
                    break;
                }
                else
                {
                    fileExtensionType = false;
                }
            }
        }
        else
        {
            fileExtensionType = true;
        }
        Flag = fileExtensionType;
        return (Flag);
    }
    public string Bind_Collected_by(String collected)
    {
        try
        {
            DataSet ds = new DataSet();
            hstParameter.Clear();
            hstParameter.Add("SrNo", collected);
            hstParameter.Add("Action", "Bind_Collected_by");
            ds.Merge((DataSet)GetDataset("sp_Consent_Letters_OutSide", CommandType.StoredProcedure, hstParameter));
            //-----
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    Collected_by = ds.Tables[0].Rows[0]["Collected_by"].ToString();
                }
            }
        }
        catch (Exception ex) { }
        return (Collected_by);
    }
	public Boolean chkdate(TextBox txt, Label lbl)
    {
        try
        {
            if (txt.Text.Length == 10)
            {
                DateTime dt = new DateTime();
                dt = Convert.ToDateTime(txt.Text);
                string onlydate = dt.Date.ToString();
                txt.Text = onlydate.Substring(0, 10);
                return (true);
            }
            else
            {
                lbl.Text = "Invalid Date Please enter Date As (DD/MM/YYYY)";
                return (false);
            }
        }
        catch (Exception ex)
        {
            lbl.Visible = true;
            lbl.Text = "Invalid Date Format";
            return (false);
        }

    }
    private void AttachParameters(DbCommand command, Hashtable hstParameters)
    {
        try
        {
            IEnumerator parameters = hstParameters.Keys.GetEnumerator();
            
            if (providerName == "System.Data.OracleClient")
            {

            }
            else
            {
                while (parameters.MoveNext())
                {
                    DbParameter param = Provider.CreateParameter();
                    param.ParameterName = "@" + parameters.Current;
                    param.Value=hstParameters[parameters.Current];
                    command.Parameters.Add(param);
                }
            }
        }
        catch (Exception exception)
        {
            HttpContext.Current.Response.Write("Error :" + exception.Message + "</br> Source : " + exception.Source);
        }
    }
    public DbCommand AttachParameters(DbCommand command,Hashtable Parameters,String strCommand)
    {
        IEnumerator ienumParams = Parameters.GetEnumerator();
        while (ienumParams.MoveNext())
        {
            DbParameter param = Provider.CreateParameter();
            param.ParameterName = "@" + ienumParams.Current;
            param.SourceColumn = Parameters[ienumParams.Current].ToString();
            param.SourceVersion = DataRowVersion.Original;
            //param.Value = hstParameters[parameters.Current];
            command.Parameters.Add(param);
        }
        return command;
    }
    public DbCommand CreateDbCommand(String commandText, CommandType commandType)
    {
        DbCommand command = Provider.CreateCommand();

        try
        {
            command.Connection = conn;
            command.CommandText = commandText;
            command.CommandType = commandType;
        }
        catch (Exception exception)
        {
            HttpContext.Current.Response.Write("Error :" + exception.Message + "</br> Source : " + exception.Source);
        }
        return command;
    }
    public void CreateDbCommand(Hashtable hstCommands, Hashtable hstCommandTypes)
    {
        Hashtable hstDbCommands = new Hashtable();
        IEnumerator ienum = hstCommands.Keys.GetEnumerator();
        try
        {
            while (ienum.MoveNext())
            {
                DbCommand command = Provider.CreateCommand();
                command.Connection = conn;
                command.CommandText = hstCommands[ienum.Current].ToString();
                command.CommandType = (CommandType)hstCommandTypes[ienum.Current];
                hstDbCommands.Add(ienum.Current, command);
            }
        }
        catch (Exception exception)
        {
            HttpContext.Current.Response.Write("Error :" + exception.Message + "</br> Source : " + exception.Source);
        }
    }
    public DbDataAdapter SetDataAdapter(String SelectCommand, String InsertCommand, String UpdateCommand, String DeleteCommand, Hashtable Parameters)
    {
        DbDataAdapter daResult = Provider.CreateDataAdapter();

        try
        {
            daResult.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            daResult.SelectCommand = (DbCommand)AttachParameters(CreateDbCommand(SelectCommand, CommandType.Text), Parameters, "");
            daResult.SelectCommand = (DbCommand)AttachParameters(CreateDbCommand(InsertCommand, CommandType.Text), Parameters, "");
            daResult.SelectCommand = (DbCommand)AttachParameters(CreateDbCommand(UpdateCommand, CommandType.Text), Parameters, "");
            daResult.SelectCommand = (DbCommand)AttachParameters(CreateDbCommand(DeleteCommand, CommandType.Text), Parameters, "");
            return daResult;
        }
        catch (Exception exception)
        {
            HttpContext.Current.Response.Write("Error :" + exception.Message + "</br> Source : " + exception.Source);
        }
        return daResult;
    }
    private DataSet GetDataSet(String commandText, CommandType commandType, Hashtable hstParameters, String tableName)
    {
        try
        {
            DbCommand command = (DbCommand)CreateDbCommand(commandText, commandType);
            if (hstParameters != null)
            {
                if (hstParameters.Count > 0)
                    AttachParameters(command, hstParameters);
            }
            using (DataSet dataset = new DataSet())
            using (DbDataAdapter adapter = Provider.CreateDataAdapter())
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                adapter.SelectCommand = command;
                if (tableName != "" && tableName != null)
                    adapter.Fill(dataset, tableName);
                else
                    adapter.Fill(dataset);
                return dataset;
            }
        }
        catch (Exception ex) { }
        return new DataSet();
    }
    public void ExecuteDataSet(ref DataSet dsResult, Hashtable hstCommands, Hashtable hstCommandTypes, Actions action, String tableName, Hashtable hstParameters) { }

    public DataSet GetDataset(String commandText, CommandType commandType)
    {
        return GetDataSet(commandText, commandType, null, null);
    }
    public DataSet GetDataset(String commandText, CommandType commandType, String tableName)
    {
        return GetDataSet(commandText, commandType, null, tableName);
    }
    public DataSet GetDataset(String commandText, CommandType commandType, Hashtable hstParameters)
    {
        return GetDataSet(commandText, commandType,hstParameters, null);
    }
    public DataSet GetDataset(String commandText, CommandType commandType, Hashtable hstParameters, String tableName)
    {
        return GetDataSet(commandText, commandType, hstParameters,tableName);
    }
    public DbDataReader GetDataReader(String commandText, CommandType commandType, Hashtable hstParameters)
    {
        if (conn.State.ToString().Equals("Closed"))
            conn.Open();
        DbCommand command = Provider.CreateCommand();
        command.Connection = conn;
        command.CommandText=commandText;
        command.CommandType = commandType;
        if (hstParameters.Count != 0)
            AttachParameters(command, hstParameters);
        return command.ExecuteReader(CommandBehavior.CloseConnection);
    }
    public DbDataReader GetDataReader(String commandText, CommandType commandType)
    {
        if (conn.State.ToString().Equals("Closed"))
            conn.Open();
        DbCommand command = Provider.CreateCommand();
        command.Connection = conn;
        command.CommandText = commandText;
        command.CommandType = commandType;
        return command.ExecuteReader(CommandBehavior.CloseConnection);
    }
    public Int32 ExecuteNonQuery(String commandText, CommandType commandType, Hashtable parameters)
    {
        DbCommand command = Provider.CreateCommand();
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        command.Connection = conn;
        command.CommandText = commandText;
        command.CommandType = commandType;
        if (parameters != null)
        {
            if (parameters.Count > 0)
                AttachParameters(command, parameters);
        }
        Int32 iRowAffected = command.ExecuteNonQuery();
        if (conn.State == ConnectionState.Open)
            conn.Close();
        return iRowAffected;
    }

    public object ExecuteScaler(String commandText, CommandType commandType, Hashtable parameters)
    {
        DbCommand command = Provider.CreateCommand();
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        command.Connection = conn;
        command.CommandText = commandText;
        command.CommandType = commandType;
        if (parameters != null)
        {
            if (parameters.Count > 0)
                AttachParameters(command, parameters);
        }
        object result = command.ExecuteScalar();
        if (conn.State == ConnectionState.Open)
            conn.Close();
        return result;
    }

    public int ExecuteScaler(String commandText)
    {
        DbCommand command = Provider.CreateCommand();
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        command.Connection = conn;
        command.CommandText = commandText;                
        int result = Convert.ToInt32(command.ExecuteScalar());
        if (conn.State == ConnectionState.Open)
            conn.Close();
        return result;
    }
    public string ExecuteScalerStr(String commandText)
    {
        DbCommand command = Provider.CreateCommand();
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        command.Connection = conn;
        command.CommandText = commandText;
        string result = command.ExecuteScalar().ToString();
        if (conn.State == ConnectionState.Open)
            conn.Close();
        return result;
    }


    public void CreateAdpt(DataSet ds,string qry,string tblMap)
    {
        DbDataAdapter adpt = Provider.CreateDataAdapter();
        DbCommand cmd = Provider.CreateCommand();
        DbCommandBuilder builder = Provider.CreateCommandBuilder();
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = qry;
        cmd.Connection = conn;
        adpt.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        adpt.SelectCommand = cmd;
        builder.DataAdapter = adpt;
        adpt.InsertCommand = builder.GetInsertCommand();
        adpt.UpdateCommand = builder.GetUpdateCommand();
        adpt.DeleteCommand = builder.GetDeleteCommand();
        adpt.Update(ds, tblMap);
    }
    public DbDataAdapter ExecuteDataAdapter(DataSet ds, String qry, String qry_Type, Hashtable parameters, String TableName)
    {
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        DbDataAdapter adapter = Provider.CreateDataAdapter();
        DbCommand cmd = Provider.CreateCommand();
        IEnumerator ienumParam = parameters.Keys.GetEnumerator();
        while (ienumParam.MoveNext())
        {
            if (providerName == "System.Data.SqlClient")
            {
                DbParameter param1 = Provider.CreateParameter();
                param1.ParameterName = "@" + ienumParam.Current;
                param1.Value = parameters[ienumParam.Current];
                cmd.Parameters.Add(param1);
            }
            else
            {

            }
        }
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = qry;
        cmd.Connection = conn;

        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        if (qry_Type == "Insert")
        {
            adapter.InsertCommand = (DbCommand)cmd.ExecuteScalar();
        }
        else if (qry_Type == "Update")
        {
            adapter.UpdateCommand = (DbCommand)cmd.ExecuteScalar();
        }
        else if (qry_Type == "Delete")
        {
            adapter.DeleteCommand = (DbCommand)cmd.ExecuteScalar();
        }
        else
        {
            adapter.SelectCommand = (DbCommand)cmd.ExecuteScalar();
        }
        adapter.Update(ds, TableName);
        DbDataAdapter DT = Provider.CreateDataAdapter();
        return DT;
    }
    public bool IsPage_Accessible(string Login_Id, string script_Name)
    {
        bool flag = false;

        string[] script_Name_Arr = script_Name.Trim('/').Split('/');
        if (script_Name_Arr != null)
        {
            if (Convert.ToBoolean(script_Name_Arr.Length))
            {
                script_Name = script_Name_Arr[script_Name_Arr.Length - 1].Trim();
                script_Name_Arr = script_Name.Split('?');
                script_Name = script_Name_Arr[0].Trim();
            }
            else
            {
                script_Name = "";
            }
        }
        else
        {
            script_Name = "";
        }

        if (script_Name != "")
        {
            DataSet dsSql = new DataSet();
            hstParameter.Clear();
            hstParameter.Add("Login_Id", Login_Id);
            hstParameter.Add("Script_Name", script_Name);
            dsSql = GetDataset("Sp_IsPage_Accessible", CommandType.StoredProcedure, hstParameter);
            if (dsSql.Tables.Count > 0)
            {
                if (dsSql.Tables[0].Rows.Count > 0)
                {
                    if (Convert.ToInt64(dsSql.Tables[0].Rows[0]["Res"].ToString().Trim()) > 0)
                    {
                        flag = true;
                    }
                    else
                    {
                        flag = false;
                    }
                }
                else
                {
                    flag = false;
                }
            }
            else
            {
                flag = false;
            }
        }
        return (flag);
    }

    public void DbConnectionClose()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
            //conn.Dispose();
        }
    }
}
